package com.zhangli.Model;

/**
 * Created by zhangli on 2017/8/28.
 */

import android.content.Context;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;

import org.w3c.dom.Document;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLDecoder;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

public class TaskDataBaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "lunchlist.db";//数据库名称
    private static final int SCHEMA_VERSION = 3;//版本号,则是升级之后的,升级方法请看onUpgrade方法里面的判断

    public TaskDataBaseHelper(Context context) {//构造函数,接收上下文作为参数,直接调用的父类的构造函数
        super(context, DATABASE_NAME, null, SCHEMA_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {//创建的是一个午餐订餐的列表,id,菜名,地址等等
        db.execSQL("CREATE TABLE tasks (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, priority INTEGER, type TEXT, content TEXT, date DATE, status INTEGER);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion == 2 && newVersion == 3) {//升级判断,如果再升级就要再加两个判断,从1到3,从2到3
            db.execSQL("ALTER TABLE tasks ADD status INTEGER;");
        }
    }

    public Cursor getAll(String where, String orderBy) {//返回表中的数据,where是调用时候传进来的搜索内容,orderby是设置中传进来的列表排序类型
        StringBuilder buf = new StringBuilder("SELECT _id, name, priority, type, content, date, status FROM tasks");

        if (where != null) {
            buf.append(" WHERE ");
            buf.append(where);
        }

        if (orderBy != null) {
            buf.append(" ORDER BY ");
            buf.append(orderBy);
        }

        return (getReadableDatabase().rawQuery(buf.toString(), null));
    }

    public Cursor getById(String id) {//根据点击事件获取id,查询数据库
        String[] args = {id};
        return (getReadableDatabase().rawQuery("SELECT _id, name, priority, type, content, date, status FROM tasks WHERE _id=?",args));
    }

    public void insert(String name,String content, String priority) {
        ContentValues cv = new ContentValues();
        cv.put("name", name);
        cv.put("priority", priority);
        //cv.put("type", type);
        cv.put("content", content);
        cv.put("status", 0);
        cv.put("date", System.currentTimeMillis());
        //cv.putNull("date=date()");
        getWritableDatabase().insert("tasks", "name", cv);
    }

    public void insert(String name,String content , String date, String status) {
        ContentValues cv = new ContentValues();
        cv.put("name", name);
        //cv.put("priority", "");
        //cv.put("type", "");
        cv.put("content", content);
        cv.put("status", status);
        cv.put("date", date);
        //cv.putNull("date=date()");
        getWritableDatabase().insert("tasks", "name", cv);
    }

    public void update(String id, String name,String content, String priority) {
        ContentValues cv = new ContentValues();
        String[] args = {id};
        cv.put("name", name);
        cv.put("priority", priority);
        //cv.put("type", type);
        cv.put("content", content);
        //cv.put("phone", phone);
        //cv.putNull("date=date()");
        getWritableDatabase().update("tasks", cv, "_ID=?",args);
    }

    public void update_status(String id, int status) {
        ContentValues cv = new ContentValues();
        String[] args = {id};
        cv.put("status", status);
        //cv.put("phone", phone);
        //cv.putNull("date=date()");
        getWritableDatabase().update("tasks", cv, "_ID=?",args);
    }

    public String getId(Cursor c) {
        String ret = c.getString(0);
        return ret==null?"":ret;
    }

    public String getName(Cursor c) {
        String ret = c.getString(1);
        return ret==null?"":ret;
    }

    public String getPriority(Cursor c) {
        String ret = c.getString(2);
        return ret==null?"":ret;
    }

    public String getType(Cursor c) {
        String ret = c.getString(3);
        return ret==null?"":ret;
    }

    public String getContent(Cursor c) {
        String ret = c.getString(4);
        return ret==null?"":ret;
    }

    public String getDate(Cursor c) {
        String t = c.getString(5);
        if(t==null)
            return "";
        try
        {
            t = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(Long.parseLong(t)));
        }
        catch(Exception e)
        {

        }
        return t;
    }
    public String getStatus(Cursor c) {
        String ret = c.getString(6);
        return ret==null?"0":ret;
    }

    public boolean import_by_D(String path)
    {
        InputSource is = null;
        try {
            is = new InputSource(new FileInputStream(path));
            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = factory.newDocumentBuilder();
            Document doc = builder.parse(is);
            NodeList nl = doc.getFirstChild().getChildNodes();
            for(int i = 0; i < nl.getLength();i++)
            {
                Node n = nl.item(i);
                NamedNodeMap a = n.getAttributes();
                if (a == null)
                    continue;
                Node s = a.getNamedItem("subject");
                Node c = a.getNamedItem("complete");
                String text = s.getNodeValue();
                String complete = c.getNodeValue();
                text = URLDecoder.decode(text,"UTF-8");
                System.out.print(text + complete);

                insert(text,"","");
            }
            return true;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (SAXException e) {
            e.printStackTrace();
        } catch (ParserConfigurationException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean export_as_csv(String path)
    {
        try {
            File file = new File(path);
            OutputStream os = new FileOutputStream(file);
            DataOutputStream dos = new DataOutputStream(os);
            Cursor c =  getAll(null,null);
            while(c.moveToNext()) {
                String sep="^";
                String content = getContent(c).replace(sep," ").replace("\n","\\n").replace("\r","\\r");
                String name = getName(c).replace(sep," ").replace("\n","\\n").replace("\r","\\r");

                String line=getId(c)+sep+name+sep+content+sep+getDate(c)+sep+getStatus(c)+"\n";
                dos.write(line.getBytes("UTF-8"));
            }
            c.close();
            dos.close();
            return true;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean import_as_csv(String path)
    {
        try {
            BufferedReader dos = new BufferedReader(new FileReader(path));
            String line;
            while((line = dos.readLine())!=null) {
                String item[] = line.split("\\^");
                if(item.length!= 5)
                    continue;
                item[1] = item[1].replace("\\n","\n").replace("\\r","\r");
                item[2] = item[2].replace("\\n","\n").replace("\\r","\r");
                insert(item[1],item[2],item[3],item[4]);
            }
            dos.close();
            return true;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }
}